Daily Invoice Export
Introduction
This is an export file that consists of invoices and credits that are generated on the day of export.
Purpose
Suppliers using visn platform requires a compilation of invoices and credits that are generated on the day of export as a single which they can further use for the internal monitoring purposes and cross checking the billing issued by customers.
File format
Comma Separated Values - *.csv
Terminology
Record Set | A set of rows exported into the CSV file, it consists of the records in the order as in below
* n denotes an index of parts/fluids added in a job line of specific enquiry | |||||||||||||||||||||||||||||||||
Record | A particular line of the export that is either of indicators allowed in a record set, along with one record for the entire file among the following |
Records
File Header - HE
- This record is mandatory
- This is the first line of the export file
- This line does not belong to a record set, instead common for the whole file
Sr.No | Field Label | Data Type | Length | Notes | Mandatory |
---|---|---|---|---|---|
1 | Record Type Indicator | Text | 2 | Must be "HE" | Y |
2 | Record Version Number | Integer | 2 | Must be "6" | Y |
3 | Scheme Operator Reference | Integer | 7 | ViSN Customer Account Number | Y |
4 | Type of Transaction | Text | 1 | - Must be "I". - The export process includes both invoices and credits in a single file. However, For HE Record the "Transaction Type" field doesn't differentiate between them, consistently displaying "Invoice" - The "Transaction Type" field acts like a flag indicating the file format for export in HE record, rather than the actual type of transaction (invoice or credit). | Y |
5 | File Serial Number | Integer | 8 | Sequential Count from 1 Each lease company (identified by /leasecompany/{leaseId}) will have a counter within its export settings (at /exports/settings). This counter, named exportFileSequenceNumber, will keep track of the number of export files generated The value from the counter is fetched into the column at the time of export | Y |
6 | Run Date | Integer | 8 | CCYYMMDD (e.g., "20031225") | Y |
7 | Run Time | Integer | 6 | HHMMSS (e.g., "232830") | Y |
8 | End of Record Marker | Control | 1 | Carriage Return Code (ASCII 10) | Y |
Scheme Operator Address Detail - LC
- This line depends on a flag
addLCLineForInvoice
which is set in the scheduler of a lease company. - If
addLCLineForInvoice
is set to false, then LC line is available only once in the file. - If
addLCLineForInvoice
is set to true, then LC line is available for each record in the file. - If
addLCLineForInvoice
is not set in the scheduler of a lease company , then LC line is available for each record in the file. - Contains the details of the customer for which the export is being generated.
Sr.No | Field Label | Data Type | Length | Notes | Mandatory |
---|---|---|---|---|---|
1 | Record Type Indicator | Text | 2 | Must be "LC" | Y |
2 | Record Type Version Number | Integer | 2 | Must be "6" | Y |
3 | Scheme Operator Reference | Integer | 7 | ViSN Customer Account Number Within each lease record in Firebase, stored under the path /leasecompany/{leaseId}, a nested field named leaseDetails holds further details about the lease. This leaseDetails field includes another nested field named accountNumber that specifically stores the account number associated with the lease. This value will be fetched into the column at the time of export. | Y |
4 | Scheme Operator Account Name | Text | 30 |
| Y |
5 | Scheme Operator Address 1 | Text | 30 |
| Y |
6 | Scheme Operator Address 2 | Text | 30 |
| Y |
7 | Scheme Operator Address 3 | Text | 30 |
| |
8 | Scheme Operator Address 4 | Text | 30 |
| |
9 | Scheme Operator Address 5 | Text | 30 |
| |
10 | Scheme Operator Postcode | Text | 8 |
| |
11 | End of Record Marker | Control | 1 | Carriage Return Code (ASCII 10) | Y |
Transaction Header - TH
- Record is mandatory for each transaction in the record set
- More than one record can exist in each file
Sr.No | Field Label | Data Type | Length | Notes | Mandatory |
---|---|---|---|---|---|
1 | Record Type Indicator | Text | 2 | Must be "TH" | Y |
2 | Record Type Version Number | Integer | 2 | Must be "6" | Y |
3 | ViSN Enquiry Number | Integer | 8 | For invoice transactions (transaction type 'I'), enquiry number will be fetched into this column. For credit transactions (transaction type 'C'), prepend the enquiry number with "1000". In other words, add "1000" to the left side of the enquiry number. For example, if the enquiry number is 6248, the resulting padded number would be "10006248". | Y |
4 | Invoice Extract Analysis Ref. | Integer | 2 | Constant "01" | Y |
5 | ViSN Enquiry Number | Text | 10 | This column will be populated with the enquiry number | |
6 | Vehicle Registration | Text | 14 | This column will be populated with the VRM (Vehicle Registration Mark) of the vehicle for which enquiry/job is raised | Y |
7 | Odometer Reading | Integer | 7 | This column captures the mileage from the jobsheet, indicating the vehicle's mileage at the time of repair. | Y (except for credits) |
8 | Transaction Date | Integer | 8 | CCYYMMDD (e.g., "20031225") For both invoices and credit records, the system will utilize the vehicle's check-in time at the garage as the standardized transaction date. | Y |
9 | Asset Number | Text | 10 | The asset number of the vehicle associated with the enquiry will be populated into this column | Y |
10 | Card Number | Text | 19 | Blank Space | |
11 | Driver Name | Text | 40 | The driver name of the vehicle associated with the enquiry will be populated into this column | |
12 | Purchase Order Number | Text | 20 | Mandatory if job authorised on the ViSN platform. Purchase Order that is captured in enquiry will be populated into this column | |
13 | Transaction Serial Number | Integer | 8 | Constant '1' | Y |
14 | Job Completion Date | Integer | 8 | CCYYMMDD (e.g., "20031225"). The date the job was closed and marked as 'Job Complete' | Y |
15 | VIN | Text | 20 | The VIN (Vehicle Identification Number) of the vehicle associated with the enquiry will be populated into this column | |
16 | End of Record Marker | Control | 1 | Carriage Return Code (ASCII 10) | Y |
Supplier/Repairer Details - RE
- Record is mandatory in each record set
- One record can exist for each transaction
- Contains the details of the supplier that carried out the service for the respective enquiry
Sr.No | Field Label | Data Type | Length | Notes | Mandatory |
---|---|---|---|---|---|
1 | Record Type Indicator | Text | 2 | Must be "RE" | Y |
2 | Record Type Version Number | Integer | 2 | Must be "6" | Y |
3 | ViSN Repairer Account Number | Integer | 7 | DI Group Supplier ID, if no group then DI Supplier ID Fetched from field named groupId from group record stored at path /leasecompany/{leaseId}/suppliers/{accountId} | Y |
4 | Supplier Account Number | Text | 20 | Supplier Group ID, if no group then Supplier ID Fetched from field named supplierId from group record stored at path/ leasecompany /{leaseId} / suppliers / {accountId} / serviceUnits / {serviceUnitObjectID} | Y |
5 | Supplier Account Name | Text | 30 | Supplier Name from Invoice Supplier Details | Y |
6 | Supplier Account Address 1 | Text | 30 | number from Supplier Address Details of Invoice Supplier Details | Y |
7 | Supplier Account Address 2 | Text | 30 | address1 from Supplier Address Details of Invoice Supplier Details | Y |
8 | Supplier Account Address 3 | Text | 30 | address2 from Supplier Address Details of Invoice Supplier Details | |
9 | Supplier Account Address 4 | Text | 30 | county from Supplier Address Details of Invoice Supplier Details | |
10 | Supplier Account Postcode | Text | 8 | postcode from Supplier Address Details of Invoice Supplier Details | |
11 | Supplier VAT number | Text | 10 | VAT Number from Invoice Supplier Details | Y |
12 | Scheme Operator Controller ID | Text | 10 | Constant Blank | |
13 | Supplier Account Address 5 | Text | 30 | Constant Blank | |
14 | Contact Telephone Number | Text | 15 | A phone number stored in a field named 'contact' of firebase document at / accounts / {accountId} / serviceUnits / {serviceUnitId} | |
15 | End of Record Marker | Control | 1 | Carriage Return Code (ASCII 10) | Y |
Invoice Credit Note Details - IN
- Record is mandatory in each record set
- Only one record can exist in each transaction
- More than one record can exist in each file
Sr.No | Field Label | Data Type | Length | Notes | Mandatory | ||||
---|---|---|---|---|---|---|---|---|---|
1 | Record Type Indicator | Text | 2 | Must be "IN" | Y | ||||
2 | Record Type Version Number | Integer | 2 | Must be "6" | Y | ||||
3 | Transaction Type | Text | 1 | I = Invoice (or) C = Credit | Y | ||||
4 | Invoice Number | Text | 20 | ViSN Invoice Number
| Y | ||||
5 | Tax Point Date | Integer | 8 | CCYYMMDD (e.g., "20031225")
| Y | ||||
6 | Repairer Invoice Text | Text | 16 | Constant Blank | |||||
7 | Original Invoice Number | Text | 20 |
| Y | ||||
8 | Original ViSN Transaction Number | Integer | 8 |
| |||||
9 | Reason for Credit | Text | 30 |
| |||||
10 | Scheme Operator Message | Text | 80 | - Not In Use | |||||
11 | Scheme Operator Authorisation Code | Text | 10 (20)* | - Not In Use | |||||
12 | Invoice Date | Integer | 8 | CCYYMMDD The date the invoice is generated will be recorded in this field for both 'I' and 'C' transactions (invoice and credit, respectively). | Y | ||||
13 | Terms Status | Text | 1 | Constant 'I', I denotes `In terms` here | |||||
14 | End of record Marker | Control | 1 | Carriage Return Code (ASCII 10) | Y |
Item Line Details - IL
- At least one record must exist in each record set
- More than one record can exist in each transaction and so in the export file
Sr.No | Field Label | Data Type | Length | Notes | Mandatory |
---|---|---|---|---|---|
1 | Record Type Indicator | Text | 2 | Must be "IL" | Y |
2 | Record Type Version Number | Integer | 2 | Must be "6" | Y |
3 | Item Number | Integer | 2 | This column indicates the specific job item's position (order) within the job sheet associated with the enquiry. | Y |
4 | Cost Type | Text | 1 | Will be one of the Cost Types Exception: For Tyre job lines, this value will always be 'M'. | Y |
5 | Charge Indicator | Text | 1 | Will be one of the Recharge Indicators | Y |
6 | Quantity | Number | 2.2 | (e.g., 99.99) For regular job lines (non-tyre), this value is always 1. However, for tyre jobs, it represents the intended number of tyre replacements on the vehicle. | Y |
7 | Additional Information Text (or) Repairer Authorisation Text | Text | 30 | This column will capture the reason provided by the customer when they manually authorize a job line. | |
8 | External Code/ViSN Job Code | Text | 5 | Integration code to be used in invoice exports.
| |
9 | Job line-Item Description | Text | 90 | Description of the Jobline Item The values for this field differ based on the job line type (details provided below)
| |
10 | Asset ID | Text | 40 | The asset number of the vehicle associated with the enquiry will be populated into this column | |
11 | Full Recharge Text | Text | 150 | This column will contain the recharge notes entered by the customer for each job line associated with recharge details. | |
12 | Action code | Text | 3 | Default on file is code R&R | |
13 | Reason Code | Text | 3 | The values for this field differ based on the job line type (details provided below)
| Y |
14 | Goodwill Percentage | Number | 3.2 | (e.g. 999.99) Will be 0.00 by default Max allowed is 100.00 | N |
15 | Labour Time | Number | 5.2 | When Cost Type is "N", is mandatory (e.g., 99999.99) Will be always set to 0 for TYRE joblines This Column will be populated from labour details modal of the jobline | Y |
16 | Labour Rate | Number | 5.2 | When Cost Type is "N", is mandatory (e.g., 99999.99) Will be always set to 0 for TYRE joblines This Column will be populated from labour details modal of the jobline | Y |
17 | Number of Parts | Number | 3.2 | When Cost Type is "N", is mandatory (e.g., 999.99) The values for this field differ based on the job line type and job description type (details provided below)
| Y |
18 | Recharge Reason Code | Text | 5 | When Charge Indicator = "R", is mandatory. Will be one of the Recharge reason codes | |
19 | Recharge Amount | Number | 5.2 | (e.g., 99999.99) This column will contain the recharge amount entered by the customer for each job line associated with recharge details. | |
20 | Recharge Percentage | Number | 3.2 | (e.g., 999.99) This column will contain the recharge percentage entered by the customer for each job line associated with recharge details. | |
21 | Recharge Text | Text | 40 | This column will contain the recharge notes entered by the customer for each job line associated with recharge details. | |
22 | End of Record Marker | Control | 1 | Carriage Return Code (ASCII 10) | Y |
Parts Record - IP
- Record is optional, available only if any of the job lines of the job sheet has parts/fluids added to them
- More than one record can exist in each file
Sr.No | Field Label | Data Type | Length | Notes | Mandatory |
---|---|---|---|---|---|
1 | Record Type Indicator | Text | 2 | Must be "IP" | Y |
2 | Record Type Version Number | Integer | 2 | Must be "6" | Y |
3 | Item Number | Integer | 2 | This column indicates the specific job item's position (order) within the job sheet associated with the enquiry. | Y |
4 | Parts Number | Integer | 1 | always "1" | Y |
5 | Parts Code | Text | 24 | During data export, the 'Part line type' field is used to determine the content for this specific column.
| |
6 | Parts Description | Text | 30 | During data export, the 'Part line type' field is used to determine the content for this specific column.
| Y |
7 | Quantity | Number | 2.2 | e.g., 13.3 for fluid denoting 13ltr 300ml 12 for parts denoting no.of.items | Y |
8 | Unit Price | Number | 5.2 | Price of the part per unit Price of the fluid per litre (e.g. 99999.99) | Y |
9 | Parts Discount Rate | Number | 3.2 | Discount percentage offered on the specific part or fluid (e.g. 999.99) | Y |
10 | Goodwill Percentage | Number | 3.2 | Will be constant 0.00 for Fluids Will be 0.00 by default and Max allowed is 100.00 for parts (e.g. 999.99) | N |
11 | Discounted Unit Price | Number | 5.2 | The unit price after discounts is calculated. To determine the Discounted Unit Price, we first apply the unit discount percentage to the unit price. Then, we apply the goodwill discount percentage to the discounted price to calculate the final discounted unit price. Example Calculation:
| Y |
12 | Nett Parts Price | Number | 5.2 | Will be the net price of the part line To determine the net price, we first calculate the total cost by multiplying the unit price by the quantity. Then, we apply two discounts: a unit discount percentage and a goodwill discount percentage, both based on the initial total cost. (e.g. 99999.99) | Y |
13 | VAT Rate | Number | 2.2 | Mandatory with Invoice or Credit Note (e.g. 99.99) | |
14 | Normal Part | Text | 1 | constant 'Y' is exported. “Y” = Yes “N” = No | Y |
15 | Manufacturer of Part | Text | 3 | constant blank is exported. Mandatory if Normal Part = “N”, however ViSN never exports N for now | |
16 | VAT Code | Text | 1 | Will be one of the VAT codes | Y |
17 | End of Record Marker | Control | 1 | Carriage Return Code (ASCII 10) | Y |
Total Line Cost Record - TL
- At least one record must exist in each record set
- More than one record can exist in each file
Sr.No | Field Label | Data Type | Length | Notes | Mandatory |
---|---|---|---|---|---|
1 | Record Type Indicator | Text | 2 | Must be "TL" | Y |
2 | Record Type Version Number | Integer | 2 | Must be "6" | Y |
3 | Nett Labour Cost | Number | 5.2 | (e.g., 99999.99) The values for this field differ based on the job line type and job description type (details provided below)
| Y |
4 | VAT Rate Labour | Number | 2.2 | (e.g., 99.99) Mandatory only with an invoice or credit note. | Y |
5 | Total Nett Parts Cost | Number | 5.2 | (e.g., 99999.99) The values for this field differ based on the job line type and job description type (details provided below)
| Y |
6 | Total Nett Line Cost | Number | 5.2 | (e.g., 99999.99) The final cost is calculated differently depending on the description type. For non-catalogue descriptions, it's the sum of net labor and parts costs from individual lines. For catalogue descriptions, it's directly set to the corresponding catalogue price. | Y |
7 | VAT Code | Text | 1 | Will be one of the VAT codes | Y |
8 | End of Record Marker | Control | 1 | Carriage Return Code (ASCII 10) | Y |
Total Transaction Cost Record - TC
- Must exist in each record set
- Only one record per Transaction
- More than one record can exist in an export file
Sr.No | Field Label | Data Type | Length | Notes | Mandatory |
---|---|---|---|---|---|
1 | Record Type Indicator | Text | 2 | Must be "TC" | Y |
2 | Record Type Version Number | Integer | 2 | Must be "6" | Y |
3 | Total Nett Labour Cost | Number | 5.2 | (e.g., 99999.99) The values for this field differ based on the job line type and job description type (details provided below)
| Y |
4 | Total Nett Parts Cost | Number | 5.2 | (e.g., 99999.99) The values for this field differ based on the job line type and job description type (details provided below)
| Y |
5 | Total Nett Transaction Cost | Number | 5.2 | (e.g., 99999.99) The final cost is calculated differently depending on the description type. For non-catalogue descriptions, it's the sum of net labor and parts costs from individual lines. For catalogue descriptions, it's directly set to the corresponding catalogue price. | Y |
6 | VAT Amount | Number | 5.2 | Mandatory with an invoice or credit note (e.g., 99999.99) | |
7 | Total Gross Labour Cost | Number | 5.2 | Mandatory with an invoice or credit note (e.g., 99999.99) The final value is calculated by factoring in the total net labor cost and applying the VAT rate | |
8 | Total Gross Parts Cost | Number | 5.2 | Mandatory with an invoice or credit note (e.g., 99999.99) The final value is calculated by factoring in the total net parts cost and applying the VAT rate | |
9 | Total Gross Transaction Cost | Number | 5.2 | Mandatory with an invoice or credit note (e.g., 99999.99) The final value is calculated by factoring in the total net jobline cost and applying the VAT rate | Y |
10 | End of Record Marker | Control | 1 | Carriage Return Code (ASCII 10) | Y |
VAT Summary Record - VA
- One record per VAT code in each Transaction
- More than one record can exist in a file
- More than one record can exist in a Transaction
Sr.No | Field Label | Data Type | Length | Notes | Mandatory |
---|---|---|---|---|---|
1 | Record Type Indicator | Text | 2 | Must be "VA" | Y |
2 | Record Type Version Number | Integer | 2 | Must be "6" | Y |
3 | VAT Rate | Number | 2.2 | (e.g., 99.99) | Y |
4 | VAT Applicable Amount | Number | 5.2 | (e.g., 99999.99) | Y |
5 | VAT Amount | Number | 5.2 | (e.g., 99999.99) | Y |
6 | VAT Code | Text | 1 | Will be one of the VAT codes | Y |
7 | End of Record Marker | Control | 1 | Carriage Return Code (ASCII 10) | Y |
Transaction Trailer Record - TT
- One record must exist for each Transaction
- More than one record can exist in an export file
Sr.No | Field Label | Format | Length | Notes | Mandatory |
---|---|---|---|---|---|
1 | Record Type Indicator | Text | 2 | Must be "TT" | Y |
2 | Record Version Number | Integer | 2 | Must be "6" | Y |
3 | Number of Records | Integer | 5 | Total number of records between Transaction Header and Transaction Trailer inclusive (excludes authorisation details) | Y |
4 | End of Record Marker | Control | 1 | Carriage Return Code (ASCII 10) | Y |
File Trailer Record - FT
- This record is mandatory
- This line does not belong to a record set, instead common for the whole file
Sr.No | Field Label | Format | Length | Notes | Mandatory |
---|---|---|---|---|---|
1 | Record Type Indicator | Text | 2 | Must be "FT" | Y |
2 | Record Version Number | Integer | 2 | Must be "6" | Y |
3 | Number of Records | Integer | 5 | Total number of transactions | Y |
4 | End of Record Marker | Control | 1 | Carriage Return Code (ASCII 10) | Y |
Setup Required
Database Configuration
Although this export is targeted to any lease company, The use of External SMR Codes is limited to few lease companies.
To prevent the external SMR Codes in the daily invoice export for all lease companies a , A flag needs to be enabled in the lease company's export configuration to restrict the usage of external SMR
location: /leasecompany/[leaseId]
attribute: includeExternalSMRCodesInExport
value: true
Note: This step is optional as there is provision available for enabling the same from Edit Customer Screen of DI Admin Portal
Generate Export
A Scheduler should be created cloud scheduler with the following parameters
- Name: Generate daily invoice export
- Region: europe-west2
- Frequency: 30 23 * * *
- TimeZone: GMT London
- Topic: projects/biddirect-2/topics/generateDailyInvoiceExport
- Message body: a JSON object including the following parameters
- dateTimeStamp: in format yyyy-MMM-dd ex 2022-Sep-30 , when skipped it will default to current date
- leaseId: id of the lease company that the export is desired to be generated for
- exportFileName: file name prefix to be used for the export file generated, when skipped defaults to 'invoice'
- addLCLineForInvoice:
- True: When set to true, an LC line is included for each record in the file.
- False: When set to false, a single LC line is included for the entire file.
Status: Accepted
Category: Protected
Authored By: Rama on Sep 01, 2023
Revisions
03 Jun, 2024 - Resolved Documentation Discrepancy, VN-12905
23 Apr, 2024 - Update Post paybill Integration, VN-11017
09 Feb, 2024 - Update Post FNP Integration, VN-11235
11 Jan, 2024 - Change content in daily invoice export, VN-10934
20 Nov, 2023 - changes with respect to code
22 Sep, 2023 - Add Daily Invoice Export Documentation, VN-9668
26 Sep, 2024 - Update LC line dependency on addLCLineForInvoice flag, VN-14508
26 Nov, 2024 - Update External Code field notes in IL line, VN-15156
06 Dec, 2024 - Update Setup Required section and add a helpful image.